--------------------------------------------------------
--  Fichier créé - mercredi-juin-19-2013   
--------------------------------------------------------





--------------------------------------------------------
--  DDL for Index CONTRAT_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."CONTRAT_PK" ON "EDT"."CONTRAT" ("IDC")
--------------------------------------------------------
--  DDL for Index FORMATION_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."FORMATION_PK" ON "EDT"."FORMATION" ("IDF")
--------------------------------------------------------
--  DDL for Index FORMATION_UK1
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."FORMATION_UK1" ON "EDT"."FORMATION" ("IDCF", "GRADE", "NIVEAU", "FILIERE", "PARCOURS", "TYPEE")
--------------------------------------------------------
--  DDL for Index PERIODE_UK1
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."PERIODE_UK1" ON "EDT"."PERIODE" ("IDP", "IDSEM")
--------------------------------------------------------
--  DDL for Index PK_IDCR
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."PK_IDCR" ON "EDT"."AGENDA" ("IDCR")
--------------------------------------------------------
--  DDL for Index PK_IDEC
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."PK_IDEC" ON "EDT"."EC" ("IDEC")
--------------------------------------------------------
--  DDL for Index PK_IDI
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."PK_IDI" ON "EDT"."INTERVENANT" ("IDI")
--------------------------------------------------------
--  DDL for Index PK_IDJ
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."PK_IDJ" ON "EDT"."JOUR" ("IDJ")
--------------------------------------------------------
--  DDL for Index PK_IDP
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."PK_IDP" ON "EDT"."PROMOTION" ("IDP")
--------------------------------------------------------
--  DDL for Index PK_IDS
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."PK_IDS" ON "EDT"."SALLE" ("IDS")
--------------------------------------------------------
--  DDL for Index PK_IDSEM
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."PK_IDSEM" ON "EDT"."SEMESTRE" ("IDSEM")
--------------------------------------------------------
--  DDL for Index PK_IDSTAT
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."PK_IDSTAT" ON "EDT"."STATUT" ("IDSTAT")
--------------------------------------------------------
--  DDL for Index PK_UE
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."PK_UE" ON "EDT"."UE" ("IDUE")
--------------------------------------------------------
--  DDL for Index SESSIONS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."SESSIONS_PK" ON "EDT"."SESSIONS" ("KEYS")
--------------------------------------------------------
--  DDL for Index UNIQUE_AG_IDI
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_AG_IDI" ON "EDT"."AGENDA" ("IDI", "IDJ", "DEBUT", "FIN")
--------------------------------------------------------
--  DDL for Index UNIQUE_AG_IDS
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_AG_IDS" ON "EDT"."AGENDA" ("IDS", "IDJ", "DEBUT", "FIN")
--------------------------------------------------------
--  DDL for Index UNIQUE_EC
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_EC" ON "EDT"."EC" ("NOM")
--------------------------------------------------------
--  DDL for Index UNIQUE_EC_CODE
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_EC_CODE" ON "EDT"."EC" ("CODE")
--------------------------------------------------------
--  DDL for Index UNIQUE_EC_FORMATION
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_EC_FORMATION" ON "EDT"."EC_UE" ("IDEC", "IDF")
--------------------------------------------------------
--  DDL for Index UNIQUE_IDI
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_IDI" ON "EDT"."INTERVENANT" ("NOM", "PRENOM", "TEL")
--------------------------------------------------------
--  DDL for Index UNIQUE_IDI_IDEC
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_IDI_IDEC" ON "EDT"."INTERV_EC" ("IDI", "IDEC")
--------------------------------------------------------
--  DDL for Index UNIQUE_IDI_VOEUX
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_IDI_VOEUX" ON "EDT"."FICHEVOEUX" ("IDI", "PERIODE", "ANNEE", "IDJOUR")
--------------------------------------------------------
--  DDL for Index UNIQUE_JOUR_DATE
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_JOUR_DATE" ON "EDT"."JOUR" ("DATEE")
--------------------------------------------------------
--  DDL for Index UNIQUE_PROM_FOR_ANNEE
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_PROM_FOR_ANNEE" ON "EDT"."PROMOTION" ("IDF", "ANNEE")
--------------------------------------------------------
--  DDL for Index UNIQUE_SEM_IDF
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_SEM_IDF" ON "EDT"."SEMESTRE" ("IDF", "NPERIODE")
--------------------------------------------------------
--  DDL for Index UNIQUE_STAT_NOM
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_STAT_NOM" ON "EDT"."STATUT" ("NOM")
--------------------------------------------------------
--  DDL for Index UNIQUE_UE
--------------------------------------------------------

  CREATE UNIQUE INDEX "EDT"."UNIQUE_UE" ON "EDT"."UE" ("IDF", "NOM")
--------------------------------------------------------
--  Constraints for Table AGENDA
--------------------------------------------------------

  ALTER TABLE "EDT"."AGENDA" ADD CONSTRAINT "UNIQUE_AG_IDI" UNIQUE ("IDI", "IDJ", "DEBUT", "FIN") ENABLE
  ALTER TABLE "EDT"."AGENDA" ADD CONSTRAINT "UNIQUE_AG_IDS" UNIQUE ("IDS", "IDJ", "DEBUT", "FIN") ENABLE
  ALTER TABLE "EDT"."AGENDA" ADD CONSTRAINT "PK_IDCR" PRIMARY KEY ("IDCR") ENABLE
  ALTER TABLE "EDT"."AGENDA" ADD CHECK (visible in ('true','fals')) ENABLE
  ALTER TABLE "EDT"."AGENDA" ADD CHECK (valide in ('true','fals')) ENABLE
  ALTER TABLE "EDT"."AGENDA" MODIFY ("VISIBLE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("VALIDE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("TYPEE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("NUMG" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("FIN" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("DEBUT" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("IDJ" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("IDI" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("IDEC" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("IDS" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("IDP" NOT NULL ENABLE)
  ALTER TABLE "EDT"."AGENDA" MODIFY ("IDCR" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table CONTRAT
--------------------------------------------------------

  ALTER TABLE "EDT"."CONTRAT" ADD CONSTRAINT "CONTRAT_PK" PRIMARY KEY ("IDC") ENABLE
  ALTER TABLE "EDT"."CONTRAT" MODIFY ("ANNEEEX" NOT NULL ENABLE)
  ALTER TABLE "EDT"."CONTRAT" MODIFY ("IDC" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table EC
--------------------------------------------------------

  ALTER TABLE "EDT"."EC" ADD CONSTRAINT "UNIQUE_EC_CODE" UNIQUE ("CODE") ENABLE
  ALTER TABLE "EDT"."EC" MODIFY ("CODE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."EC" ADD CONSTRAINT "UNIQUE_EC" UNIQUE ("NOM") ENABLE
  ALTER TABLE "EDT"."EC" ADD CONSTRAINT "PK_IDEC" PRIMARY KEY ("IDEC") ENABLE
  ALTER TABLE "EDT"."EC" MODIFY ("NOM" NOT NULL ENABLE)
  ALTER TABLE "EDT"."EC" MODIFY ("IDEC" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table EC_UE
--------------------------------------------------------

  ALTER TABLE "EDT"."EC_UE" ADD CONSTRAINT "UNIQUE_EC_FORMATION" UNIQUE ("IDEC", "IDF") ENABLE
  ALTER TABLE "EDT"."EC_UE" MODIFY ("COEFF" NOT NULL ENABLE)
  ALTER TABLE "EDT"."EC_UE" MODIFY ("NBRTD" NOT NULL ENABLE)
  ALTER TABLE "EDT"."EC_UE" MODIFY ("NBRCM" NOT NULL ENABLE)
  ALTER TABLE "EDT"."EC_UE" MODIFY ("IDUE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."EC_UE" MODIFY ("IDEC" NOT NULL ENABLE)
  ALTER TABLE "EDT"."EC_UE" MODIFY ("IDF" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table FICHEVOEUX
--------------------------------------------------------

  ALTER TABLE "EDT"."FICHEVOEUX" ADD CONSTRAINT "UNIQUE_IDI_VOEUX" UNIQUE ("IDI", "PERIODE", "ANNEE", "IDJOUR") ENABLE
  ALTER TABLE "EDT"."FICHEVOEUX" MODIFY ("POIDS" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FICHEVOEUX" MODIFY ("NCRENEAU" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FICHEVOEUX" MODIFY ("IDJOUR" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FICHEVOEUX" MODIFY ("ANNEE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FICHEVOEUX" MODIFY ("PERIODE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FICHEVOEUX" MODIFY ("IDI" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table FORMATION
--------------------------------------------------------

  ALTER TABLE "EDT"."FORMATION" ADD CONSTRAINT "FORMATION_UK1" UNIQUE ("IDCF", "GRADE", "NIVEAU", "FILIERE", "PARCOURS", "TYPEE") ENABLE
  ALTER TABLE "EDT"."FORMATION" ADD CONSTRAINT "FORMATION_PK" PRIMARY KEY ("IDF") ENABLE
  ALTER TABLE "EDT"."FORMATION" MODIFY ("IDCF" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FORMATION" MODIFY ("TYPEE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FORMATION" MODIFY ("PARCOURS" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FORMATION" MODIFY ("FILIERE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FORMATION" MODIFY ("NIVEAU" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FORMATION" MODIFY ("GRADE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."FORMATION" MODIFY ("IDF" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table INTERV_EC
--------------------------------------------------------

  ALTER TABLE "EDT"."INTERV_EC" ADD CONSTRAINT "UNIQUE_IDI_IDEC" UNIQUE ("IDI", "IDEC") ENABLE
  ALTER TABLE "EDT"."INTERV_EC" MODIFY ("IDEC" NOT NULL ENABLE)
  ALTER TABLE "EDT"."INTERV_EC" MODIFY ("IDI" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table INTERVENANT
--------------------------------------------------------

  ALTER TABLE "EDT"."INTERVENANT" ADD CONSTRAINT "UNIQUE_IDI" UNIQUE ("NOM", "PRENOM", "TEL") ENABLE
  ALTER TABLE "EDT"."INTERVENANT" ADD CONSTRAINT "PK_IDI" PRIMARY KEY ("IDI") ENABLE
  ALTER TABLE "EDT"."INTERVENANT" MODIFY ("IDSTAT" NOT NULL ENABLE)
  ALTER TABLE "EDT"."INTERVENANT" MODIFY ("TEL" NOT NULL ENABLE)
  ALTER TABLE "EDT"."INTERVENANT" MODIFY ("PASSWD" NOT NULL ENABLE)
  ALTER TABLE "EDT"."INTERVENANT" MODIFY ("MAIL" NOT NULL ENABLE)
  ALTER TABLE "EDT"."INTERVENANT" MODIFY ("PRENOM" NOT NULL ENABLE)
  ALTER TABLE "EDT"."INTERVENANT" MODIFY ("NOM" NOT NULL ENABLE)
  ALTER TABLE "EDT"."INTERVENANT" MODIFY ("IDI" NOT NULL ENABLE)
  ALTER TABLE "EDT"."INTERVENANT" MODIFY ("ENSERVICE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."INTERVENANT" ADD CONSTRAINT "CHECK_CONSTRAINT_ENSERVICE" CHECK (enService IN
      (
       'true',
       'fals'
      )
   ) ENABLE
--------------------------------------------------------
--  Constraints for Table JOUR
--------------------------------------------------------

  ALTER TABLE "EDT"."JOUR" ADD CONSTRAINT "UNIQUE_JOUR_DATE" UNIQUE ("DATEE") ENABLE
  ALTER TABLE "EDT"."JOUR" ADD CONSTRAINT "PK_IDJ" PRIMARY KEY ("IDJ") ENABLE
  ALTER TABLE "EDT"."JOUR" ADD CHECK (feriee in ('true','fals')) ENABLE
  ALTER TABLE "EDT"."JOUR" MODIFY ("FERIEE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."JOUR" MODIFY ("DATEE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."JOUR" MODIFY ("IDJ" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table PERIODE
--------------------------------------------------------

  ALTER TABLE "EDT"."PERIODE" ADD CONSTRAINT "PERIODE_UK1" UNIQUE ("IDP", "IDSEM") ENABLE
  ALTER TABLE "EDT"."PERIODE" MODIFY ("FIN" NOT NULL ENABLE)
  ALTER TABLE "EDT"."PERIODE" MODIFY ("DEBUT" NOT NULL ENABLE)
  ALTER TABLE "EDT"."PERIODE" MODIFY ("IDSEM" NOT NULL ENABLE)
  ALTER TABLE "EDT"."PERIODE" MODIFY ("IDP" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table PROMOTION
--------------------------------------------------------

  ALTER TABLE "EDT"."PROMOTION" ADD CONSTRAINT "UNIQUE_PROM_FOR_ANNEE" UNIQUE ("IDF", "ANNEE") ENABLE
  ALTER TABLE "EDT"."PROMOTION" ADD CONSTRAINT "PK_IDP" PRIMARY KEY ("IDP") ENABLE
  ALTER TABLE "EDT"."PROMOTION" MODIFY ("ANNEE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."PROMOTION" MODIFY ("IDF" NOT NULL ENABLE)
  ALTER TABLE "EDT"."PROMOTION" MODIFY ("IDP" NOT NULL ENABLE)
  ALTER TABLE "EDT"."PROMOTION" MODIFY ("NBRGRP" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table SALLE
--------------------------------------------------------

  ALTER TABLE "EDT"."SALLE" ADD CONSTRAINT "PK_IDS" PRIMARY KEY ("IDS") ENABLE
  ALTER TABLE "EDT"."SALLE" MODIFY ("BATIMENT" NOT NULL ENABLE)
  ALTER TABLE "EDT"."SALLE" MODIFY ("NOM" NOT NULL ENABLE)
  ALTER TABLE "EDT"."SALLE" MODIFY ("NUM" NOT NULL ENABLE)
  ALTER TABLE "EDT"."SALLE" MODIFY ("IDS" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table SEMESTRE
--------------------------------------------------------

  ALTER TABLE "EDT"."SEMESTRE" ADD CONSTRAINT "UNIQUE_SEM_IDF" UNIQUE ("IDF", "NPERIODE") ENABLE
  ALTER TABLE "EDT"."SEMESTRE" ADD CONSTRAINT "PK_IDSEM" PRIMARY KEY ("IDSEM") ENABLE
  ALTER TABLE "EDT"."SEMESTRE" MODIFY ("NPERIODE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."SEMESTRE" MODIFY ("IDF" NOT NULL ENABLE)
  ALTER TABLE "EDT"."SEMESTRE" MODIFY ("IDSEM" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table SESSIONS
--------------------------------------------------------

  ALTER TABLE "EDT"."SESSIONS" ADD CONSTRAINT "SESSIONS_PK" PRIMARY KEY ("KEYS") ENABLE
  ALTER TABLE "EDT"."SESSIONS" MODIFY ("EXPIRE" NOT NULL ENABLE)
  ALTER TABLE "EDT"."SESSIONS" MODIFY ("KEYS" NOT NULL ENABLE)
  ALTER TABLE "EDT"."SESSIONS" MODIFY ("IDI" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table STATUT
--------------------------------------------------------

  ALTER TABLE "EDT"."STATUT" ADD CONSTRAINT "UNIQUE_STAT_NOM" UNIQUE ("NOM") ENABLE
  ALTER TABLE "EDT"."STATUT" ADD CONSTRAINT "PK_IDSTAT" PRIMARY KEY ("IDSTAT") ENABLE
  ALTER TABLE "EDT"."STATUT" MODIFY ("NBRHSUP" NOT NULL ENABLE)
  ALTER TABLE "EDT"."STATUT" MODIFY ("NBRH" NOT NULL ENABLE)
  ALTER TABLE "EDT"."STATUT" MODIFY ("NOM" NOT NULL ENABLE)
  ALTER TABLE "EDT"."STATUT" MODIFY ("IDSTAT" NOT NULL ENABLE)
--------------------------------------------------------
--  Constraints for Table UE
--------------------------------------------------------

  ALTER TABLE "EDT"."UE" ADD CONSTRAINT "UNIQUE_UE" UNIQUE ("IDF", "NOM") ENABLE
  ALTER TABLE "EDT"."UE" ADD CONSTRAINT "PK_UE" PRIMARY KEY ("IDUE") ENABLE
  ALTER TABLE "EDT"."UE" MODIFY ("IDSEM" NOT NULL ENABLE)
  ALTER TABLE "EDT"."UE" MODIFY ("NOM" NOT NULL ENABLE)
  ALTER TABLE "EDT"."UE" MODIFY ("IDF" NOT NULL ENABLE)
  ALTER TABLE "EDT"."UE" MODIFY ("IDUE" NOT NULL ENABLE)
--------------------------------------------------------
--  Ref Constraints for Table AGENDA
--------------------------------------------------------

  ALTER TABLE "EDT"."AGENDA" ADD CONSTRAINT "FK_AG_IDEC" FOREIGN KEY ("IDEC") REFERENCES "EDT"."EC" ("IDEC") ENABLE
  ALTER TABLE "EDT"."AGENDA" ADD CONSTRAINT "FK_AG_IDI" FOREIGN KEY ("IDI") REFERENCES "EDT"."INTERVENANT" ("IDI") ENABLE
  ALTER TABLE "EDT"."AGENDA" ADD CONSTRAINT "FK_AG_IDJ" FOREIGN KEY ("IDJ") REFERENCES "EDT"."JOUR" ("IDJ") ENABLE
  ALTER TABLE "EDT"."AGENDA" ADD CONSTRAINT "FK_AG_IDP" FOREIGN KEY ("IDP") REFERENCES "EDT"."PROMOTION" ("IDP") ENABLE
  ALTER TABLE "EDT"."AGENDA" ADD CONSTRAINT "FK_AG_IDS" FOREIGN KEY ("IDS") REFERENCES "EDT"."SALLE" ("IDS") ENABLE
--------------------------------------------------------
--  Ref Constraints for Table EC_UE
--------------------------------------------------------

  ALTER TABLE "EDT"."EC_UE" ADD CONSTRAINT "FK_IDEC" FOREIGN KEY ("IDEC") REFERENCES "EDT"."EC" ("IDEC") ENABLE
  ALTER TABLE "EDT"."EC_UE" ADD CONSTRAINT "FK_IDF" FOREIGN KEY ("IDF") REFERENCES "EDT"."FORMATION" ("IDF") ENABLE
  ALTER TABLE "EDT"."EC_UE" ADD CONSTRAINT "FK_IDUE" FOREIGN KEY ("IDUE") REFERENCES "EDT"."UE" ("IDUE") ENABLE
--------------------------------------------------------
--  Ref Constraints for Table FICHEVOEUX
--------------------------------------------------------

  ALTER TABLE "EDT"."FICHEVOEUX" ADD CONSTRAINT "FK_IDI_INTR" FOREIGN KEY ("IDI") REFERENCES "EDT"."INTERVENANT" ("IDI") ENABLE
--------------------------------------------------------
--  Ref Constraints for Table FORMATION
--------------------------------------------------------

  ALTER TABLE "EDT"."FORMATION" ADD CONSTRAINT "FORMATION_CONTRAT_FK1" FOREIGN KEY ("IDCF") REFERENCES "EDT"."CONTRAT" ("IDC") ENABLE
--------------------------------------------------------
--  Ref Constraints for Table INTERV_EC
--------------------------------------------------------

  ALTER TABLE "EDT"."INTERV_EC" ADD CONSTRAINT "FK_IDEC_INTRV" FOREIGN KEY ("IDEC") REFERENCES "EDT"."EC" ("IDEC") ENABLE
  ALTER TABLE "EDT"."INTERV_EC" ADD CONSTRAINT "FK_IDI_INTRV" FOREIGN KEY ("IDI") REFERENCES "EDT"."INTERVENANT" ("IDI") ENABLE
--------------------------------------------------------
--  Ref Constraints for Table INTERVENANT
--------------------------------------------------------

  ALTER TABLE "EDT"."INTERVENANT" ADD CONSTRAINT "FK_IDSTAT" FOREIGN KEY ("IDSTAT") REFERENCES "EDT"."STATUT" ("IDSTAT") ENABLE
--------------------------------------------------------
--  Ref Constraints for Table PERIODE
--------------------------------------------------------

  ALTER TABLE "EDT"."PERIODE" ADD CONSTRAINT "PERIODE_IDP" FOREIGN KEY ("IDP") REFERENCES "EDT"."PROMOTION" ("IDP") ENABLE
  ALTER TABLE "EDT"."PERIODE" ADD CONSTRAINT "PERIODE_IDSEM" FOREIGN KEY ("IDSEM") REFERENCES "EDT"."SEMESTRE" ("IDSEM") ENABLE
--------------------------------------------------------
--  Ref Constraints for Table PROMOTION
--------------------------------------------------------

  ALTER TABLE "EDT"."PROMOTION" ADD CONSTRAINT "FK_IDF_PR" FOREIGN KEY ("IDF") REFERENCES "EDT"."FORMATION" ("IDF") ENABLE
--------------------------------------------------------
--  Ref Constraints for Table SEMESTRE
--------------------------------------------------------

  ALTER TABLE "EDT"."SEMESTRE" ADD CONSTRAINT "FK_SEM_IDF" FOREIGN KEY ("IDF") REFERENCES "EDT"."FORMATION" ("IDF") ENABLE
--------------------------------------------------------
--  Ref Constraints for Table SESSIONS
--------------------------------------------------------

  ALTER TABLE "EDT"."SESSIONS" ADD CONSTRAINT "FK_SESSION_IDI" FOREIGN KEY ("IDI") REFERENCES "EDT"."INTERVENANT" ("IDI") ENABLE
--------------------------------------------------------
--  Ref Constraints for Table UE
--------------------------------------------------------

  ALTER TABLE "EDT"."UE" ADD CONSTRAINT "FK_UE" FOREIGN KEY ("IDF") REFERENCES "EDT"."FORMATION" ("IDF") ENABLE
  ALTER TABLE "EDT"."UE" ADD CONSTRAINT "FK_UE_IDSEM" FOREIGN KEY ("IDSEM") REFERENCES "EDT"."SEMESTRE" ("IDSEM") ENABLE




--------------------------------------------------------
--  DDL for Trigger T_AGENDA_CONTRAINT_UNIQ
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_AGENDA_CONTRAINT_UNIQ" 
BEFORE INSERT
ON agenda
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE 
	tot number :=0;
	n number:=0;
	msg varchar(1000) := '';
	annee1 date;
	annee2 date;
  annee date;
  laDate date;
  
  Deb agenda.debut%TYPE;
  finn agenda.fin%TYPE;
  duree number:=0;
  dureetotal number :=0; 
  
  minD agenda.debut%TYPE;
  heurD agenda.debut%TYPE;
  
  minF agenda.fin%TYPE;
  heurF agenda.fin%TYPE;

  Statut Varchar(50) := '';
  
  Nominterv Varchar(50) := '';
  Nomec Varchar(50) := '';
  Salle varchar(50) := '';
    

CURSOR Chjour IS SELECT debut,fin FROM agenda ag where ag.idj = :new.idj 
                                                  and ag.idi = :new.idi;

CURSOR Chsemaine IS SELECT ag.debut,ag.fin  from agenda ag, jour j1, jour j2
                          where ag.idi=:new.idi
                          and  j1.idj=:new.idj
                          and j2.datee between 
                                      (j1.datee-(to_char(j1.datee,'d'))) 
                                and(j1.datee+(5-(to_char(j1.datee,'d'))))
                          and j2.idj= ag.idj;
                          
CURSOR CnbrHEC IS 
SELECT sum(
((TRUNC(fin/100)*60)+MOD(fin,100)) - ((TRUNC(debut/100)*60)+MOD(debut,100)))  
from agenda
where idec = :new.idec and idp = :new.idp
and ((:new.typee = 'cm' and typee ='cm') 
or (:new.typee <>'cm' and typee <>'cm'));
                    
                    
CURSOR CnbrHCM IS 
Select Sum(
((Trunc(Ag.Fin/100)*60)+Mod(Ag.Fin,100)) - ((Trunc(ag.Debut/100)*60)+Mod(ag.Debut,100)))  
From Agenda Ag, Promotion P, Promotion P2
Where Typee='cm' and Ag.Idi = :New.Idi And P.Idp = :New.Idp 
and p2.annee = p.annee and ag.idp = p2.idp;


CURSOR CnbrHTDTP IS 
Select Sum(
((Trunc(Ag.Fin/100)*60)+Mod(Ag.Fin,100)) - ((Trunc(ag.Debut/100)*60)+Mod(ag.Debut,100)))  
From Agenda Ag, Promotion P, Promotion P2
Where Typee<>'cm' And Ag.Idi = :New.Idi And P.Idp = :New.Idp 
And P2.Annee = P.Annee And Ag.Idp = P2.Idp;


	contrainte_uniq_violated EXCEPTION;
  
Begin
	/*msg := msg || ' erreurs :'||CHR(10);*/
	
  if (:new.debut < 800 or :new.debut > 2000)then 
    msg := msg || '*) l heure de debut du creneau n est pas valide'||chr(10);
		tot := tot +1;
	end if;
  
  if (:new.fin < 829 or :new.fin > 2030)then 
    msg := msg || '*) l heure de fin du creneau n est pas valide'||chr(10);
		tot := tot +1;
	end if;
  
  If (:New.Debut >= :New.Fin)Then 
    Msg := Msg || '*) l heure de début du creneau ne peut pas etre inférieur à h fin'||Chr(10);
		Tot := Tot +1;
	end if;
  
  
	select count(idi) into n from agenda 
  where :new.idj = idj 
  and :new.idi = idi 
  and ( (:new.debut >= debut and :new.debut < fin) 
    or (:new.debut <= debut and :new.fin > debut)
    );
    
	if(n > 0) then
		msg := msg || '*) l enseignant a '||n||' creneau(s) qui est(sont) programmé(s) durant cet intervale de temps'||chr(10);
		tot := tot +n;
	end if;

	select count(idcr) into n 
        from agenda
        where :new.idj = idj
        and :new.ids = ids 
        and 
            ( (:new.debut >= debut and :new.debut < fin) 
          or (:new.debut <= debut and :new.fin > debut));
	if (n>0)then 
		msg := msg || '*) salle il existe '||n||' creneau(s) qui est(sont) programmé(s) pour cette salle durant cet intervale de temps'||chr(10);
		tot := tot +n;
	end if;
	
	
	select datee into annee 
              from jour 
              where :new.idj = jour.idj;
              
	select to_date('20-08-'||annee,'dd-mm-yyyy') into annee1 
              from promotion 
              where idp= :new.idp;
  annee2 := ADD_months(annee1,12);
  
  --msg := msg||chr(10)||annee1||chr(10)||annee||chr(10)||annee2||chr(10);
	if(annee1 > annee or annee2 < annee)then
    msg := msg || '*) le créneau à ajouter est en dehors de la periode de l annee scolaire de la promotion'||chr(10);
		tot := tot +1;
  end if;
  
	select count(idp) into n 
          from agenda 
          where :new.idj = idj 
          and :new.idp = idp 
          and :new.numG = numG 
          and
         ( (:new.debut >= debut and :new.debut < fin) 
        or (:new.debut <= debut and :new.fin > debut));
	if (n>0)then 
		msg := msg || '*) cette promotion a '||n||' creneau(s) qui est(sont) déja programmé(s) pour cet intervale de temps'||chr(10);
		tot := tot +n;
	end if;
  
  
  select count(ec_ue.idec) into n 
          from ec_ue,promotion  
          where promotion.idp = :new.idp  
          and promotion.idf = ec_ue.idf 
          and ec_ue.idec = :new.idec;
      
	if (n = 0 or n>1)then 
    msg := msg || '*) idEC n est pas valide, la formation de cette promotion ne contient pas ec mentioné '||chr(10);
		tot := tot +1;
	end if;
  
  
  select count(idj) into n from jour  where idj = :new.idj  
      and feriee='true';
      
	if (n <> 0)then 
    msg := msg || '*) le jour est fériée'||chr(10);
		tot := tot +1;
	end if;
        
  /*vérifier si l'enseignant est en service ou non !*/      
  select count(idi) into n from intervenant where idi = :new.idi and enservice='true';
  if (n <> 1)then 
    msg := msg || '*) l enseignant n est plus en service !'||chr(10);
		tot := tot +1;
	end if;
  
  /*Cette requete vérifie l'existance de la fiche de voeux de l intervenant dans
  le cas ou elle n existe pas ça veut dire qu il n est pas diponible.
  */
  
             
  select count(f.idi) into n from fichevoeux f, promotion p, jour j 
      where f.idi = :new.idi 
      and j.idj = :new.idj
      and f.idjour = to_char(j.datee,'d') 
      and f.annee = p.annee 
      and p.idp = :new.idp
      and f.poids > 0
      and ( (  f.periode = 'S1' and ( extract(MONTH FROM j.datee) > 8 
                                    or extract(MONTH FROM j.datee) = 1) )  
         or (f.periode = 'S2' and  extract(MONTH FROM  j.datee) > 1 
                                and extract(MONTH FROM  j.datee) < 9  ) )
      and ((:new.debut < 1200 and (f.ncreneau =1 or f.ncreneau =3)) or 
           (:new.debut > 1200 and (f.ncreneau =2 or f.ncreneau =3)) );         
            
	if (n = 0)then
    msg := msg || '*) La fiche de voeux de l enseignant indique qu il n est pas disponible'||chr(10);
		tot := tot +1;
	end if;

/*Cette requete vérifie le nombre d heure de travail de l enseignant par jour (<=8h) !! il faut utiliser les curseurs !!*/
/*
OPEN Chjour;

dureetotal := 0;
minD := MOD(:new.debut, 100);
heurD := TRUNC (:new.debut / 100);
minF := MOD(:new.fin,100);
heurF := TRUNC(:new.fin/100);
duree := ((heurF * 60) + minF)-((heurD * 60)+minD);
dureetotal := dureetotal +duree;

LOOP
FETCH Chjour INTO deb, finn;
EXIT WHEN Chjour%NOTFOUND;
minD := MOD(deb, 100);
heurD := TRUNC (deb / 100);
minF := MOD(finn,100);
heurF := TRUNC(finn/100);
duree := ((heurF * 60) + minF)-((heurD * 60)+minD);
dureetotal := dureetotal +duree;
END LOOP;

if (dureetotal > 480)then
    msg := msg || '*) l enseignant ne peut pas travailler plus de 8h par jour'||chr(10);
		tot := tot +1;
	end if;

CLOSE Chjour;
*/


/*Cette requete vérifie le nombre d heure de travail de l enseignant par semaine (<=36h)*/

/*
OPEN Chsemaine;
dureetotal := 0;
minD := MOD(:new.debut, 100);
heurD := TRUNC (:new.debut / 100);
minF := MOD(:new.fin,100);
heurF := TRUNC(:new.fin/100);
duree := ((heurF * 60) + minF)-((heurD * 60)+minD);
dureetotal := dureetotal +duree;

LOOP
FETCH Chsemaine INTO deb, finn;
EXIT WHEN Chsemaine%NOTFOUND;
minD := MOD(deb, 100);
heurD := TRUNC (deb / 100);
minF := MOD(finn,100);
heurF := TRUNC(finn/100);
duree := ((heurF * 60) + minF)-((heurD * 60)+minD);
dureetotal := dureetotal +duree;
END LOOP;


if (dureetotal > 2160)then
    msg := msg || '*) l enseignant ne peut pas travailler plus de 36h par semaine'||chr(10);
		tot := tot +1;
end if;

CLOSE Chsemaine;
*/

/*vérification qu'on dépasse pas la quantité autorisé en nbr heure liée à la formation*/

OPEN CnbrHEC;
dureetotal := 0;
minD := MOD(:new.debut, 100);
heurD := TRUNC (:new.debut / 100);
minF := MOD(:new.fin,100);
heurF := TRUNC(:new.fin/100);
duree := ((heurF * 60) + minF)-((heurD * 60)+minD);
dureetotal := dureetotal +duree;

LOOP
FETCH CnbrHEC INTO duree;
EXIT WHEN CnbrHEC%NOTFOUND;
dureetotal := dureetotal +duree;
END LOOP;


if(:new.typee='cm')then
  select e.nbrcm into n from ec_ue e,promotion p
    where e.idec = :new.idec 
    and e.idf= p.idf 
    and p.idp = :new.idp 
    and p.idf=e.idf;
else
    select e.nbrtd into n from ec_ue e,promotion p
    where e.idec = :new.idec 
    and e.idf= p.idf 
    and p.idp = :new.idp 
    and p.idf=e.idf;
end if;  

If (Dureetotal > (N*60))Then
    msg := msg || '*) Cet Ec dépasse le nombre d heure autorisé qui est définit sur 
    la plaquette de la formation. Le dépassement est de '||(dureetotal - (n*60))||' minutes '||chr(10);
		tot := tot +1;
end if;

CLOSE CnbrHEC;
  
  
/*vérification que l'enseignant ne dépasse pas la quantité heure définit selon son statut*/

OPEN CnbrHCM;
dureetotal := 0;
minD := MOD(:new.debut, 100);
heurD := TRUNC (:new.debut / 100);
minF := MOD(:new.fin,100);
heurF := TRUNC(:new.fin/100);
Duree := ((Heurf * 60) + Minf)-((Heurd * 60)+Mind);
if (:new.typee='cm') then Duree := duree *1.5; end if;
dureetotal := dureetotal +duree;

LOOP
FETCH CnbrHCM INTO duree;
Exit When Cnbrhcm%Notfound;
Dureetotal := Dureetotal +(Duree*1.5);
END LOOP;  
  
CLOSE CnbrHCM;


OPEN CnbrHTDTP;
  
LOOP
FETCH CnbrHTDTP INTO duree;
EXIT WHEN CnbrHTDTP%NOTFOUND;
Dureetotal := Dureetotal +Duree;
End Loop;

Select (S.Nbrh+S.Nbrhsup) Into N  
From Statut S, Intervenant I Where I.Idi = :New.Idi 
and i.idstat = s.idstat;


If (Dureetotal > (N*60) )Then
  
    Select S.Nom Into Statut From Intervenant I, Statut S Where I.Idi = :New.Idi And I.Idstat = S.Idstat; 
    If(Statut = 'Professeur' or Statut = 'Maitre de Conferences') Then
        /* Mettre un Warning !!!! */
        Statut := '';
    Else
      Msg := Msg || '*) Cet intervenant dépasse le nombre d heure définit sur 
      son statut. Le dépassement est de '||(dureetotal - (n*60))||' minutes '||chr(10);
      Tot := Tot +1;
    end if;  
end if;

CLOSE CnbrHTDTP; 
  

  if(tot > 0 ) then
	RAISE contrainte_uniq_violated;
  end if;
    
  
  EXCEPTION
	When Contrainte_Uniq_Violated Then
  Select Nom Into Nomec From Ec Where Idec=:New.Idec;
  Select Nom Into Salle From Salle Where Ids = :New.Ids;
  Select Prenom||' '||Nom Into Nominterv From Intervenant Where Idi = :New.Idi;
	Msg := Chr(10)||tot||' Erreurs :'||' Ajout créneau le '||Annee||' de '||Trunc(:New.Debut/100)||':'||
        Mod(:New.Debut, 100)||'h à '||Trunc(:New.Fin/100)||':'||
        Mod(:New.Fin, 100)||'h '||Upper(:New.Typee)||' '||Nomec||' Ens: '||
        Nominterv||' Salle: '||Salle||chr(10)||msg;
	Raise_application_error(-20324,msg);
	WHEN no_data_found THEN
	n :=0 ;
END;
ALTER TRIGGER "EDT"."T_AGENDA_CONTRAINT_UNIQ" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_AGENDA_DELETE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_AGENDA_DELETE" 
BEFORE delete
ON agenda
FOR EACH ROW
DECLARE 
	n Date;
	can_not_delete_cr EXCEPTION;
Begin
	select j.datee into n from  jour j where j.idj = :old.idj;
  if(n <  SYSDATE ) then
	Raise Can_Not_Delete_Cr;
  end if;
  EXCEPTION
	When Can_Not_Delete_Cr Then
	Raise_application_error(-20324,'1 erreur :'||CHR(10)||'Désolé vous ne pouvez pas supprimer un créneau qui a déjà été éffectuer le '||n||' .');
END;
ALTER TRIGGER "EDT"."T_AGENDA_DELETE" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_AGENDA_IDCR
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_AGENDA_IDCR" 
BEFORE INSERT
ON agenda
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idcr is null) then
  SELECT S_agenda.nextval
  INTO :new.idcr
  FROM dual;
  end if;
END;









--insert into agenda values(null,1,1,4,1,5,0800,1000,1,'td,'true','true');
ALTER TRIGGER "EDT"."T_AGENDA_IDCR" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_AGENDA_IDI_UNIQ
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_AGENDA_IDI_UNIQ" 
BEFORE INSERT
ON agenda
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE 
	n number;
	enseignant_not_dispo EXCEPTION;
  msg varchar2(500) := 'error' ;
BEGIN
	select count(idi) into n from agenda where :new.idj = idj and :new.idi = idi and 
		( (:new.debut >= debut and :new.debut < fin) or (:new.debut <= debut and :new.fin > debut));
    msg := msg ||  ' 10 ';
  if(n > 0 ) then
	RAISE enseignant_not_dispo;
  end if;
  EXCEPTION
	WHEN enseignant_not_dispo THEN
	Raise_application_error(-20324,' erreur l enseignant a '||msg||' creneau(s) qui est(sont) programmé(s) pour cet intervale de temps');
END;
ALTER TRIGGER "EDT"."T_AGENDA_IDI_UNIQ" DISABLE
--------------------------------------------------------
--  DDL for Trigger T_AGENDA_SALLE_UNIQ
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_AGENDA_SALLE_UNIQ" 
BEFORE INSERT
ON agenda
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE 
	n number;
	salle_not_dispo EXCEPTION;
BEGIN
	select count(idcr) into n from agenda where :new.idj = idj and :new.ids = ids and :new.numG = numG and
		( (:new.debut >= debut and :new.debut < fin) or (:new.debut <= debut and :new.fin > debut));
  if(n > 0 ) then
	RAISE salle_not_dispo;
  end if;
  EXCEPTION
	WHEN salle_not_dispo THEN
	Raise_application_error(-20324,'Désolé il existe '||n||' creneau(s) qui est(sont) programmé(s) pour cette salle dans cet intervale de temps');
END;
ALTER TRIGGER "EDT"."T_AGENDA_SALLE_UNIQ" DISABLE
--------------------------------------------------------
--  DDL for Trigger T_CONTRAT_IDC
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_CONTRAT_IDC" 
BEFORE INSERT
ON contrat
REFERENCING NEW AS NEW
FOR EACH ROW
Declare 
annee_not_valide EXCEPTION;
BEGIN
  if(:new.idc is null) then
  SELECT S_contrat.nextval
  INTO :new.idc
  FROM dual;
  end if;
  
  if ((:new.anneeex < TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))) or (:new.anneeex > TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))+5)) then
    RAISE annee_not_valide;
  end if;
  
  EXCEPTION
	WHEN annee_not_valide THEN
	Raise_application_error(-20324,'la date d expiration du contrat n est pas valide');
END;
ALTER TRIGGER "EDT"."T_CONTRAT_IDC" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_EC_IDEC
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_EC_IDEC" 
BEFORE INSERT
ON ec
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idec is null) then
  SELECT S_ec.nextval
  INTO :new.idec
  FROM dual;
  end if;
END;
ALTER TRIGGER "EDT"."T_EC_IDEC" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_FORMATION_IDF
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_FORMATION_IDF" 
BEFORE INSERT
ON formation
REFERENCING NEW AS NEW
FOR EACH ROW
declare 
annee number;
idc_not_valide exception;
BEGIN
  if(:new.idf is null) then
  SELECT S_formation.nextval
  INTO :new.idf
  FROM dual;
  end if;
 
  select anneeex into annee from contrat where contrat.idc = :new.idcf;
  if ((annee < TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))) or (annee > TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))+5)) then
    RAISE idc_not_valide;
  end if;
  
  EXCEPTION
	WHEN idc_not_valide THEN
	Raise_application_error(-20324,'erreur le contrat attribué n est pas valide');
END;
ALTER TRIGGER "EDT"."T_FORMATION_IDF" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_INTERVENANT_IDI
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_INTERVENANT_IDI" 
BEFORE INSERT
ON intervenant
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idi is null) then
  SELECT S_intervenant.nextval
  INTO :new.idi
  FROM dual;
  end if;
END;
ALTER TRIGGER "EDT"."T_INTERVENANT_IDI" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_JOUR_IDJ
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_JOUR_IDJ" 
BEFORE INSERT
ON jour
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idj is null) then
  SELECT S_jour.nextval
  INTO :new.idj
  FROM dual;
  end if;
END;
ALTER TRIGGER "EDT"."T_JOUR_IDJ" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_JOUR_UPDATE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_JOUR_UPDATE" 
BEFORE update
ON jour
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE 
	n number;
	jour_non_modifiable EXCEPTION;
	
BEGIN
	select count(idcr) into n from agenda where :new.idj = idj;
  if(n > 0 or SYSDATE > :new.datee) then
	RAISE jour_non_modifiable;
  end if;
  EXCEPTION
	WHEN jour_non_modifiable THEN
	Raise_application_error(-20324,'Désolé vous ne pouvez pas modifier le type de jour car il y a déja '||n||' creneaux qui sont programés');
END;
ALTER TRIGGER "EDT"."T_JOUR_UPDATE" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_PROMO_IDP
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_PROMO_IDP" 
BEFORE INSERT
ON promotion
REFERENCING NEW AS NEW
FOR EACH ROW
declare
	annee number;
	idf_not_valide exception;
BEGIN
  if(:new.idp is null) then
  SELECT S_promotion.nextval
  INTO :new.idp
  FROM dual;
  end if;
  
  if ((:new.annee < TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))) or (:new.annee > TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))+5)) then
    Raise Idf_Not_Valide;
  end if;
  
  select anneeex into annee from contrat ,formation where idf= :new.idf and idcf = idc;
  if ((annee < TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))) or (annee > TO_NUMBER(EXTRACT (YEAR FROM SYSDATE))+5)) then
    RAISE idf_not_valide;
  end if;
  
  EXCEPTION
	WHEN idf_not_valide THEN
	Raise_application_error(-20324,'erreur le contrat de la formation n est plus valable');
  
END;
ALTER TRIGGER "EDT"."T_PROMO_IDP" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_SALLE_IDS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_SALLE_IDS" 
BEFORE INSERT
ON salle
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.ids is null) then
  SELECT S_salle.nextval
  INTO :new.ids
  FROM dual;
  end if;
END;
ALTER TRIGGER "EDT"."T_SALLE_IDS" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_SEMESTRE_IDSEM
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_SEMESTRE_IDSEM" 
BEFORE INSERT
ON semestre
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idsem is null) then
  SELECT S_semestre.nextval
  INTO :new.idsem
  FROM dual;
  end if;
END;
ALTER TRIGGER "EDT"."T_SEMESTRE_IDSEM" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_SEMESTRE_NPERIODE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_SEMESTRE_NPERIODE" 
BEFORE insert
ON semestre
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE 
	nperiode_non_null EXCEPTION;
	
BEGIN
	
  if(:new.nperiode <= 0) then
	RAISE nperiode_non_null;
  end if;
  EXCEPTION
	WHEN nperiode_non_null THEN
	Raise_application_error(-20324,'nombre de periode non null');
END;
ALTER TRIGGER "EDT"."T_SEMESTRE_NPERIODE" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_STAT_IDSTAT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_STAT_IDSTAT" 
BEFORE INSERT
ON STATUT
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idstat is null) then
  SELECT S_status.nextval
  INTO :new.idstat
  FROM dual;
  End If;
END;
ALTER TRIGGER "EDT"."T_STAT_IDSTAT" ENABLE
--------------------------------------------------------
--  DDL for Trigger T_UE_IDUE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "EDT"."T_UE_IDUE" 
BEFORE INSERT
ON ue
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  if(:new.idue is null) then
  SELECT S_ue.nextval
  INTO :new.idue
  FROM dual;
  end if;
END;
ALTER TRIGGER "EDT"."T_UE_IDUE" ENABLE
